-- exec sp_create_wip_flush_master 0
/*
This Processure should be supplied with 0 (zero) as an argument, if it has to be run for all the lots in the System.
If for a particualr lot, Wip Information has to be updated means, it has to be supplied lotID as argument.
*/

ALTER      proc sp_create_wip_flush_master @lotId int as 
BEGIN
	DECLARE @processStep_Id INT
	DECLARE @lot_Id INT
	DECLARE @qty_In INT
	DECLARE @location_Id INT
	DECLARE @tempCount INT
	DECLARE @chkVal INT
	DECLARE @temp INT
	DECLARE @fg_qty NUMERIC
	DECLARE @date_in DATETIME
	DECLARE @currDate DATETIME
	DECLARE @CURR_YEAR INT
	DECLARE @CURRENT_QTR INT
	DECLARE @expectedDateOut DATETIME
	DECLARE @lot_Number VARCHAR(50)
	DECLARE @ps_Type_Name VARCHAR(50)
	DECLARE @path NVARCHAR(1000)
	
	DECLARE @FG_ProcessStep_Id INT
	DECLARE @Temp_ProcessStep_Id INT
	DECLARE @wafer_qty_in INT
	DECLARE @fg_date DATETIME
	
	DECLARE @processCycleTime INT
	DECLARE @numberOfDaysInStage INT
	
	DECLARE @expected_die_wafer FLOAT
	DECLARE @gross_die_per_wafer INT
	DECLARE @temp_count INT
	DECLARE @total_ship_time INT
	DECLARE @total_yeild FLOAT
	DECLARE @flag INT
	DECLARE @LOT_STATUS VARCHAR(50)
	DECLARE @ASSEMBLY_LOT_QTY INT
	
	DECLARE @fg_cursor CURSOR 
	DECLARE @wip_cursor Cursor
	
	DECLARE @fg_Table TABLE ( path NVARCHAR( 1000 ), fg_Process_Step_ID INT )
	
	DECLARE @results table ( ProcessStep_Id int, Location_Id int, Lot_Id int, Lot_Number Varchar(20), Qty_In int, Date_In DateTime, Fg_qty int, Fg_ProcessStep_Id int, Fg_date_in DateTime )
	
	--##TempTable table stores the Ship Time and Total Yield for each Process to its Finished Good Process.
	SELECT @tempCount = COUNT(*) FROM TEMPDB.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '##TempTable'
	IF @tempCount = 0
	BEGIN
		CREATE TABLE ##TempTable ( ProcessStep_Id INT, ToProcessStep_Id INT, Total_Ship_Time INT, Total_Yeild FLOAT )
	END
	ELSE
	BEGIN
		DELETE FROM ##TempTable
	END
	
	--##Process_Paths stores the possible paths and their Finished Goods ProcessStep_IDs for each Process.
	SELECT @tempCount = COUNT(*) FROM TEMPDB.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '##Process_Paths'
	IF @tempCount = 0
	BEGIN
		CREATE TABLE ##Process_Paths (ProcessStep_Id INT, Path NVARCHAR( 1000 ), Last_Process_Step_ID INT, Finished INT )
	END
	ELSE
	BEGIN
		DELETE FROM ##Process_Paths
	END
	
	--wip_cursor selects all the lot records in the LOT_TABLE whose Lot Current Status is "Running" and 
	--whose Process Step IDs match with those in the Process Step ID in the PROCESS_STEP_TABLE
	--DECLARE wip_cursor Cursor for select Lot_Id, Lot_Table.ProcessStep_Id, Location_Id, Lot_Number, ps_type_name from Lot_Table, Process_Step where Lot_Table.ProcessStep_Id = Process_Step.ProcessStep_Id and Lot_Current_Status = 'Running' --and Lot_Id = 15913
	IF  @lotId > 0 
	BEGIN
		DELETE FROM WIP_MASTER where Lot_ID = @lotId
		DELETE from Wip_Reservation_Table where Lot_ID = @lotId

		SET @wip_cursor = CURSOR for 
		SELECT LOT_ID, LOT_TABLE.PROCESSSTEP_ID, LOCATION_ID, LOT_NUMBER, PS_TYPE_NAME, 0 FROM LOT_TABLE, PROCESS_STEP WHERE LOT_TABLE.PROCESSSTEP_ID = PROCESS_STEP.PROCESSSTEP_ID AND ( LOT_CURRENT_STATUS = 'RUNNING' OR LOT_CURRENT_STATUS = 'HOLD' ) AND LOT_ID = @LOTID AND PS_TYPE_NAME <> 'WAFER STORE'  --AND PS_TYPE_NAME <> 'FINISHED GOODS'
		UNION
		SELECT LOT_ID, LOT_TABLE.PROCESSSTEP_ID, LOCATION_ID, LOT_NUMBER, PS_TYPE_NAME, 0 FROM LOT_TABLE, PROCESS_STEP WHERE LOT_TABLE.PROCESSSTEP_ID = PROCESS_STEP.PROCESSSTEP_ID AND ( LOT_CURRENT_STATUS = 'RUNNING' OR LOT_CURRENT_STATUS = 'HOLD' ) AND LOT_ID = @LOTID AND PS_TYPE_NAME = 'WAFER STORE' 
		AND LOT_ID NOT IN ( SELECT LT.LOT_ID FROM  LOT_TABLE LT, SORT_RELEASE_LOT_TABLE SRLT, SORT_RELEASE_TABLE SRT WHERE SRLT.SORT_REL_ID = SRT.SORT_REL_ID AND SRLT.LOT_ID = LT.LOT_ID AND (SRT.STATUS = 'ISSUED' OR SRT.STATUS = 'APPROVED') AND SRLT.LOT_ID = @LOTID )
		UNION
		SELECT LT.LOT_ID , SRT.PROCESSSTEP_ID, ( SELECT TOP 1 LOCATION_ID FROM LOCATION WHERE VENDOR_ID = SRT.VEND_ID AND L_TYPE = 'SORT' ) AS 'Location_Id',
		LT.LOT_NUMBER, PS_TYPE_NAME, 0	FROM  LOT_TABLE LT, SORT_RELEASE_LOT_TABLE SRLT, SORT_RELEASE_TABLE SRT, PROCESS_STEP PS
		WHERE SRLT.SORT_REL_ID = SRT.SORT_REL_ID AND SRLT.LOT_ID = LT.LOT_ID AND SRT.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND (SRT.STATUS = 'ISSUED' OR SRT.STATUS = 'APPROVED') AND SRLT.LOT_ID = @LOTID
		UNION 
		SELECT SBRT.LOT_ID, PS.PROCESSSTEP_ID, LOCATION_ID, LOT_NUMBER, 'DIE BANK' AS PS_TYPE_NAME, 0
		FROM SORT_BIN_RELEASE_TABLE SBRT, LOT_TABLE LT, PROCESS_STEP PS
		WHERE SBRT.LOT_ID = LT.LOT_ID AND LT.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND SBRT.LOT_ID = @LOTID AND 
		(ISNULL( BIN_1_BALANCE_QTY, ISNULL(BIN_1,0) ) > 0 OR 
		ISNULL( BIN_2_BALANCE_QTY, ISNULL(BIN_2,0) ) > 0 OR 
		ISNULL( BIN_3_BALANCE_QTY, ISNULL(BIN_3,0) ) > 0 OR 
		ISNULL( BIN_4_BALANCE_QTY, ISNULL(BIN_4,0) ) > 0 OR 
		ISNULL( BIN_5_BALANCE_QTY, ISNULL(BIN_5,0) ) > 0 OR
		ISNULL( BIN_6_BALANCE_QTY, ISNULL(BIN_6,0) ) > 0 OR 
		ISNULL( BIN_7_BALANCE_QTY, ISNULL(BIN_7,0) ) > 0 OR 
		ISNULL( BIN_8_BALANCE_QTY, ISNULL(BIN_8,0) ) > 0 OR 
		ISNULL( BIN_9_BALANCE_QTY, ISNULL(BIN_9,0) ) > 0 OR 
		ISNULL( BIN_10_BALANCE_QTY, ISNULL(BIN_10,0) ) > 0 OR 
		ISNULL( BIN_11_BALANCE_QTY, ISNULL(BIN_11,0) ) > 0 OR
		ISNULL( BIN_12_BALANCE_QTY, ISNULL(BIN_12,0) ) > 0 OR 
		ISNULL( BIN_13_BALANCE_QTY, ISNULL(BIN_13,0) ) > 0 OR 
		ISNULL( BIN_14_BALANCE_QTY, ISNULL(BIN_14,0) ) > 0 OR 
		ISNULL( BIN_15_BALANCE_QTY, ISNULL(BIN_15,0) ) > 0 )
		--AND SBRT.LOT_ID NOT IN ( SELECT LOT_ID FROM ASSM_RELEASE_LOT_TABLE ARLT, ASSM_RELEASE_TABLE ART WHERE ARLT.ASSEMBLY_RELEASE_ID = ART.ASSEMBLY_RELEASE_ID AND ( ART.STATUS = 'ISSUED' OR ART.STATUS = 'APPROVED' ) AND ARLT.LOT_ID = @LOTID )
		UNION
		SELECT ARLT.LOT_ID, ART.PROCESSSTEP_ID, 
		( SELECT TOP 1 LOCATION_ID FROM LOCATION WHERE VENDOR_ID = TO_VENDOR_ID AND L_TYPE = 'ASSEMBLY' ) AS 'Location_Id',
		ART.ASSEMBLY_RELEASE_NO AS 'LOT_NUMBER', 'ASSEMBLY' AS PS_TYPE_NAME, 0	FROM ASSM_RELEASE_LOT_TABLE ARLT, ASSM_RELEASE_TABLE ART 
		WHERE ARLT.ASSEMBLY_RELEASE_ID = ART.ASSEMBLY_RELEASE_ID AND ( ART.STATUS = 'ISSUED' OR ART.STATUS = 'APPROVED' ) AND ARLT.Lot_Id = @lotId
		UNION
		Select Lot_Table.Lot_Id, Lot_Table.ProcessStep_Id, Location_Id, Lot_Number, ps_type_name, 1 from Lot_Table, Process_Step, Shipped_Lot where Shipped_Lot.Lot_Id = Lot_Table.Lot_Id and Lot_Table.ProcessStep_Id = Process_Step.ProcessStep_Id and Shipped_Lot.Status = 'TRANSIT' and ps_type_Name <> 'Finished Goods' and ps_type_Name <> 'FAB' and Lot_Table.Lot_Id = @lotId
	END 
	ELSE
	BEGIN
		DELETE from WIP_Master
		DELETE from Wip_Reservation_Table
		SET @wip_cursor = CURSOR for 

		SELECT LOT_ID, LOT_TABLE.PROCESSSTEP_ID, LOCATION_ID, LOT_NUMBER, PS_TYPE_NAME, 0 FROM LOT_TABLE, PROCESS_STEP WHERE LOT_TABLE.PROCESSSTEP_ID = PROCESS_STEP.PROCESSSTEP_ID AND ( LOT_CURRENT_STATUS = 'RUNNING' OR LOT_CURRENT_STATUS = 'HOLD' ) AND PS_TYPE_NAME <> 'WAFER STORE' --AND PS_TYPE_NAME <> 'FINISHED GOODS'
		UNION
		SELECT LT1.LOT_ID, LT1.PROCESSSTEP_ID, LOCATION_ID, LOT_NUMBER, PS_TYPE_NAME, 0 FROM LOT_TABLE LT1, PROCESS_STEP PS WHERE LT1.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND ( LOT_CURRENT_STATUS = 'RUNNING' OR LOT_CURRENT_STATUS = 'HOLD' ) AND PS_TYPE_NAME = 'WAFER STORE' 
		AND LOT_ID NOT IN ( SELECT SRLT.LOT_ID FROM SORT_RELEASE_LOT_TABLE SRLT, SORT_RELEASE_TABLE SRT WHERE SRLT.SORT_REL_ID = SRT.SORT_REL_ID AND SRLT.LOT_ID = LT1.LOT_ID AND (SRT.STATUS <> 'Cancelled') )
		UNION
		SELECT LT.LOT_ID , SRT.PROCESSSTEP_ID, 
		( SELECT TOP 1 LOCATION_ID FROM LOCATION WHERE VENDOR_ID = SRT.VEND_ID AND L_TYPE = 'SORT' ) AS 'Location_Id',
		LT.LOT_NUMBER, PS_TYPE_NAME, 0	FROM  LOT_TABLE LT, SORT_RELEASE_LOT_TABLE SRLT, SORT_RELEASE_TABLE SRT, PROCESS_STEP PS
		WHERE SRLT.SORT_REL_ID = SRT.SORT_REL_ID AND SRLT.LOT_ID = LT.LOT_ID AND SRT.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND (SRT.STATUS = 'ISSUED' OR SRT.STATUS = 'APPROVED') 
		UNION 
		SELECT SBRT.LOT_ID, PS.PROCESSSTEP_ID, LOCATION_ID, LOT_NUMBER, 'DIE BANK' AS PS_TYPE_NAME, 0
		FROM SORT_BIN_RELEASE_TABLE SBRT, LOT_TABLE LT, PROCESS_STEP PS
		WHERE SBRT.LOT_ID = LT.LOT_ID AND LT.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND
		(ISNULL( BIN_1_BALANCE_QTY, ISNULL(BIN_1,0) ) > 0 OR 
		ISNULL( BIN_2_BALANCE_QTY, ISNULL(BIN_2,0) ) > 0 OR 
		ISNULL( BIN_3_BALANCE_QTY, ISNULL(BIN_3,0) ) > 0 OR 
		ISNULL( BIN_4_BALANCE_QTY, ISNULL(BIN_4,0) ) > 0 OR 
		ISNULL( BIN_5_BALANCE_QTY, ISNULL(BIN_5,0) ) > 0 OR
		ISNULL( BIN_6_BALANCE_QTY, ISNULL(BIN_6,0) ) > 0 OR 
		ISNULL( BIN_7_BALANCE_QTY, ISNULL(BIN_7,0) ) > 0 OR 
		ISNULL( BIN_8_BALANCE_QTY, ISNULL(BIN_8,0) ) > 0 OR 
		ISNULL( BIN_9_BALANCE_QTY, ISNULL(BIN_9,0) ) > 0 OR 
		ISNULL( BIN_10_BALANCE_QTY, ISNULL(BIN_10,0) ) > 0 OR 
		ISNULL( BIN_11_BALANCE_QTY, ISNULL(BIN_11,0) ) > 0 OR
		ISNULL( BIN_12_BALANCE_QTY, ISNULL(BIN_12,0) ) > 0 OR 
		ISNULL( BIN_13_BALANCE_QTY, ISNULL(BIN_13,0) ) > 0 OR 
		ISNULL( BIN_14_BALANCE_QTY, ISNULL(BIN_14,0) ) > 0 OR 
		ISNULL( BIN_15_BALANCE_QTY, ISNULL(BIN_15,0) ) > 0 )
		--AND SBRT.LOT_ID NOT IN ( SELECT LOT_ID FROM ASSM_RELEASE_LOT_TABLE ARLT, ASSM_RELEASE_TABLE ART WHERE ARLT.ASSEMBLY_RELEASE_ID = ART.ASSEMBLY_RELEASE_ID AND ( ART.STATUS = 'ISSUED' OR ART.STATUS = 'APPROVED' ) AND ARLT.LOT_ID = SBRT.LOT_ID )
		UNION
		SELECT ARLT.LOT_ID, ART.PROCESSSTEP_ID, 
		( SELECT TOP 1 LOCATION_ID FROM LOCATION WHERE VENDOR_ID = TO_VENDOR_ID AND L_TYPE = 'ASSEMBLY' ) AS 'Location_Id',
		ART.ASSEMBLY_RELEASE_NO AS 'LOT_NUMBER', 'ASSEMBLY' AS PS_TYPE_NAME, 0	FROM ASSM_RELEASE_LOT_TABLE ARLT, ASSM_RELEASE_TABLE ART 
		WHERE ARLT.ASSEMBLY_RELEASE_ID = ART.ASSEMBLY_RELEASE_ID AND ( ART.STATUS = 'ISSUED' OR ART.STATUS = 'APPROVED' ) 
		UNION 
		Select Lot_Table.Lot_Id, Lot_Table.ProcessStep_Id, Location_Id, Lot_Number, ps_type_name, 1 from Lot_Table, Process_Step, Shipped_Lot where Shipped_Lot.Lot_Id = Lot_Table.Lot_Id and Lot_Table.ProcessStep_Id = Process_Step.ProcessStep_Id and Shipped_Lot.Status = 'TRANSIT' and ps_type_Name <> 'Finished Goods' and ps_type_Name <> 'FAB'
	END
	
	SET @currDate = GETDATE()  
	SET @CURR_YEAR = YEAR( GETDATE() )
	SET @CURRENT_QTR = DATEPART( Q, GETDATE() )
	--Processing each Lot in the @wip_cursor
	OPEN @wip_cursor
	FETCH NEXT FROM @wip_cursor INTO @lot_Id, @processStep_Id, @location_Id, @lot_Number, @ps_Type_Name, @flag
	WHILE @@fetch_status = 0
	BEGIN
		--PRINT ' @lot_Id = ' + CONVERT( VARCHAR, @lot_Id ) + ' @processStep_Id = ' + CONVERT( VARCHAR, @processStep_Id ) + ' @ps_type_name = ' + CONVERT( VARCHAR, @ps_type_name ) + ' @location_Id = ' + CONVERT( VARCHAR, @location_Id ) + ' @lot_Number = ' + CONVERT( VARCHAR, @lot_Number )

		SET @processCycleTime = 0
		SET @expectedDateOut = NULL
		--Lots with Process Type Name "Finished Goods" will directly go to WIP_MASTER table
		--Quantity Out and Date Out for this Lots is same as Quantity In and Date In
		--Quantity and Date information for "Finished Goods" is available in INVENTORY_LOT table
		IF @ps_type_name = 'Finished Goods'
		BEGIN
			SELECT @qty_in = il_Qty_In, @date_in = IL_Date_In FROM Inventory_Lot WHERE Lot_Id = @lot_Id
			
			IF @date_in IS NULL
			BEGIN
				SET @date_in = @currDate
			END
			--PRINT ' fg-@qty_in = ' + CONVERT( VARCHAR, ISNULL( @qty_in, 0 ) )
			INSERT INTO WIP_Master VALUES ( @lot_Id, @lot_Number, @processStep_Id, @location_id, @qty_in, @date_in, @processStep_Id, @qty_in, @date_in, 0 )
		END 
		ELSE
		BEGIN 
			--Lots with Process Type Name "FAB" have Wafers as Qty Input. Qty In and Date In for this Lots available
			--in FAB_LOT table. Converting No. of Wafers into the equivalent No. of Dies using Gross Die/Wafer
			--(which is read from FAB_TABLE using ProcessStep_ID of this Lot).
			IF @ps_type_name = 'FAB'
			BEGIN
				SET @gross_die_per_wafer = 1
				SELECT @wafer_qty_in = FL_Input_Quantity, @date_in = FL_Date_In, @expectedDateOut = FL_EXPECTED_DATE_OUT from FAB_LOT where Lot_Id = @lot_Id
				--SELECT @gross_die_per_wafer = ISNULL( FT_Gross_Die_Wafer, 1) FROM Fab_Table WHERE ProcessStep_ID = @processStep_Id
	
				SELECT @chkVal = ISNUMERIC( ISNULL( Parameter_Value, 1 ) ) FROM Process_Parameter WHERE ProcessStep_ID = @processStep_Id AND Parameter_Key = 'Gross Die/Wafer'
				IF @chkVal = 1
				BEGIN
					SELECT @gross_die_per_wafer = ISNULL( Parameter_Value, 1 ) FROM Process_Parameter WHERE ProcessStep_ID = @processStep_Id AND Parameter_Key = 'Gross Die/Wafer'
				END
	
				IF ( @gross_die_per_wafer = 0 )
				BEGIN
					SET @gross_die_per_wafer = 1
				END
				SET @qty_in = @wafer_qty_in * @gross_die_per_wafer

				IF @flag = 1 
				BEGIN
					SELECT TOP 1 @date_in = SHIP_DATE FROM SHIPPED_LOT WHERE LOT_ID = @lot_Id ORDER BY SHIP_DATE DESC
				END
	
				SELECT TOP 1 @processCycleTime = ISNULL( PSLT_Cycle_Time_Normal, 0 ) FROM Process_Step_Location_Table where ProcessStep_ID = @processStep_Id ORDER BY PSLT_Priority

				--PRINT ' FAB-@qty_in = ' + CONVERT( VARCHAR, ISNULL( @qty_in, 0 ) )
			END
			--Lots with Process Type Name "SORT" have Qty In and Date In information in the SORT_LOT table.
			ELSE IF @ps_type_name = 'SORT'
			BEGIN
				--SELECT @wafer_qty_in = SL_Wafer_Qty, @date_in = SL_Date_In, @expectedDateOut = SL_EXPECTED_DATE_OUT FROM Sort_Lot WHERE Lot_Id = @lot_Id
				--SELECT @expected_die_wafer = ISNULL( ST_Expected_Net_Die_Wafer, 1) FROM sort_table WHERE ProcessStep_ID = @processStep_Id

				/** TODO @expectedDateOut **/
				SELECT @wafer_qty_in = QTY_PULLED, @date_in = SORT_REL_DATE FROM  LOT_TABLE LT, SORT_RELEASE_LOT_TABLE SRLT, SORT_RELEASE_TABLE SRT
				WHERE LT.LOT_ID = SRLT.LOT_ID AND SRLT.SORT_REL_ID = SRT.SORT_REL_ID AND LT.LOT_ID = @lot_Id AND (SRT.STATUS = 'ISSUED' OR SRT.STATUS = 'APPROVED')
				
				DECLARE @Fab_ProcessStep_Id varchar(50)
				SELECT TOP 1 @Fab_ProcessStep_Id = ProcessStep_ID FROM fn_get_process_flow_by_processstep_id ( @processStep_Id ) where Process_Type_Name = 'Fab'
	
				--SELECT @expected_die_wafer = ISNULL( FT_Gross_Die_Wafer, 0) FROM Fab_Table WHERE ProcessStep_ID = @Fab_ProcessStep_Id
				SET @expected_die_wafer = 1
				SELECT @chkVal = ISNUMERIC( ISNULL( Parameter_Value, 1 ) ) FROM Process_Parameter WHERE ProcessStep_ID = @Fab_ProcessStep_Id AND Parameter_Key = 'Gross Die/Wafer'
				IF @chkVal = 1
				BEGIN
					SELECT @expected_die_wafer = ISNULL( Parameter_Value, 1 ) FROM Process_Parameter WHERE ProcessStep_ID = @Fab_ProcessStep_Id AND Parameter_Key = 'Gross Die/Wafer'
				END

				IF @flag = 1 
				BEGIN
					SELECT TOP 1 @date_in = SHIP_DATE FROM SHIPPED_LOT WHERE LOT_ID = @lot_Id ORDER BY SHIP_DATE DESC
				END
	
				SET @qty_in = @wafer_qty_in * @expected_die_wafer
				SET @expected_die_wafer = null
				SELECT TOP 1 @processCycleTime = ISNULL( PSLT_Cycle_Time_Normal, 0 ) FROM Process_Step_Location_Table where ProcessStep_ID = @processStep_Id ORDER BY PSLT_Priority
				--PRINT ' SORT-@qty_in = ' + CONVERT( VARCHAR, ISNULL( @qty_in, 0 ) )
			END
			ELSE IF @ps_type_name = 'Die Bank'
			BEGIN
				--SELECT @qty_in = IL_Qty_In, @date_in = IL_Date_In FROM Inventory_Lot WHERE Lot_Id = @lot_Id

				SELECT @qty_in = (
													SUM( ISNULL( BIN_1_BALANCE_QTY, ISNULL(BIN_1,0) ) ) + 
													SUM( ISNULL( BIN_2_BALANCE_QTY, ISNULL(BIN_2,0) ) ) + 
													SUM( ISNULL( BIN_3_BALANCE_QTY, ISNULL(BIN_3,0) ) ) + 
													SUM( ISNULL( BIN_4_BALANCE_QTY, ISNULL(BIN_4,0) ) ) + 
													SUM( ISNULL( BIN_5_BALANCE_QTY, ISNULL(BIN_5,0) ) ) +
													SUM( ISNULL( BIN_6_BALANCE_QTY, ISNULL(BIN_6,0) ) ) + 
													SUM( ISNULL( BIN_7_BALANCE_QTY, ISNULL(BIN_7,0) ) ) + 
													SUM( ISNULL( BIN_8_BALANCE_QTY, ISNULL(BIN_8,0) ) ) + 
													SUM( ISNULL( BIN_9_BALANCE_QTY, ISNULL(BIN_9,0) ) ) + 
													SUM( ISNULL( BIN_10_BALANCE_QTY, ISNULL(BIN_10,0) ) ) + 
													SUM( ISNULL( BIN_11_BALANCE_QTY, ISNULL(BIN_11,0) ) ) +
													SUM( ISNULL( BIN_12_BALANCE_QTY, ISNULL(BIN_12,0) ) ) + 
													SUM( ISNULL( BIN_13_BALANCE_QTY, ISNULL(BIN_13,0) ) ) + 
													SUM( ISNULL( BIN_14_BALANCE_QTY, ISNULL(BIN_14,0) ) ) + 
													SUM( ISNULL( BIN_15_BALANCE_QTY, ISNULL(BIN_15,0) ) ) ),
				@date_in = ( SELECT TOP 1 SL_DATE_OUT FROM SORT_LOT_HISTORY WHERE LOT_ID = LT.LOT_ID AND SL_DATE_OUT IS NOT NULL AND SL_CURRENT_STATUS = 'CLOSE' )
				FROM SORT_BIN_RELEASE_TABLE SBRT, LOT_TABLE LT WHERE  SBRT.LOT_ID = LT.LOT_ID AND SBRT.LOT_ID = @lot_Id
				AND (
					ISNULL( BIN_1_BALANCE_QTY, ISNULL(BIN_1,0) ) > 0 OR 
					ISNULL( BIN_2_BALANCE_QTY, ISNULL(BIN_2,0) ) > 0 OR 
					ISNULL( BIN_3_BALANCE_QTY, ISNULL(BIN_3,0) ) > 0 OR 
					ISNULL( BIN_4_BALANCE_QTY, ISNULL(BIN_4,0) ) > 0 OR 
					ISNULL( BIN_5_BALANCE_QTY, ISNULL(BIN_5,0) ) > 0 OR
					ISNULL( BIN_6_BALANCE_QTY, ISNULL(BIN_6,0) ) > 0 OR 
					ISNULL( BIN_7_BALANCE_QTY, ISNULL(BIN_7,0) ) > 0 OR 
					ISNULL( BIN_8_BALANCE_QTY, ISNULL(BIN_8,0) ) > 0 OR 
					ISNULL( BIN_9_BALANCE_QTY, ISNULL(BIN_9,0) ) > 0 OR 
					ISNULL( BIN_10_BALANCE_QTY, ISNULL(BIN_10,0) ) > 0 OR 
					ISNULL( BIN_11_BALANCE_QTY, ISNULL(BIN_11,0) ) > 0 OR
					ISNULL( BIN_12_BALANCE_QTY, ISNULL(BIN_12,0) ) > 0 OR 
					ISNULL( BIN_13_BALANCE_QTY, ISNULL(BIN_13,0) ) > 0 OR 
					ISNULL( BIN_14_BALANCE_QTY, ISNULL(BIN_14,0) ) > 0 OR 
					ISNULL( BIN_15_BALANCE_QTY, ISNULL(BIN_15,0) ) > 0 ) 
				GROUP BY  LT.LOT_ID, LT.LOT_NUMBER, LT.LOT_CURRENT_STATUS, LT.LOCATION_ID

				IF @flag = 1 
				BEGIN
					SELECT TOP 1 @date_in = SHIP_DATE FROM SHIPPED_LOT WHERE LOT_ID = @lot_Id ORDER BY SHIP_DATE DESC
				END
				--PRINT ' DB-@qty_in = ' + CONVERT( VARCHAR, ISNULL( @qty_in, 0 ) )
			END
			--Lots with Process Type Name "Assembly" have Qty In and Date In information in the ASSEMBLY_LOT table.
			ELSE IF @ps_type_name = 'Assembly'
			BEGIN
				--SELECT @qty_in = AL_Qty_In, @date_in = AL_Date_In, @expectedDateOut = AL_EXPECTED_DATE_OUT FROM Assembly_Lot WHERE Lot_Id = @lot_Id
				--SELECT ARLT.LOT_ID, ART.ASSEMBLY_RELEASE_NO, QTY_PULLED, ASSEMBLY_RELEASE_DATE, ART.STATUS FROM ASSM_RELEASE_LOT_TABLE ARLT, ASSM_RELEASE_TABLE ART 
				--WHERE ARLT.ASSEMBLY_RELEASE_ID = ART.ASSEMBLY_RELEASE_ID  AND ART.PROCESSSTEP_ID = @PROCESSSTEP_ID AND ( ART.STATUS = 'ISSUED' OR ART.STATUS = 'APPROVED' ) AND TO_VENDOR_ID = ( SELECT TOP 1 VENDOR_ID FROM LOCATION WHERE LOCATION_ID = @P_LOCATION_ID AND L_TYPE = @PS_TYPE  )
				
				SELECT @qty_in = SUM( QTY_PULLED ), @date_in = MAX( ASSEMBLY_RELEASE_DATE ) FROM ASSM_RELEASE_LOT_TABLE ARLT, ASSM_RELEASE_TABLE ART 
				WHERE ARLT.ASSEMBLY_RELEASE_ID = ART.ASSEMBLY_RELEASE_ID  AND ARLT.Lot_Id = @lot_Id AND ( ART.STATUS = 'ISSUED' OR ART.STATUS = 'APPROVED' ) AND ART.ASSEMBLY_RELEASE_NO = @lot_Number

				SELECT @ASSEMBLY_LOT_QTY = ISNULL( AL_Qty_In, 0 ) FROM Assembly_Lot WHERE Lot_Id = @lot_Id

				SET @qty_in = ISNULL( @qty_in, 0 ) + ISNULL( @ASSEMBLY_LOT_QTY, 0 )

				SELECT TOP 1 @processCycleTime = ISNULL( PSLT_Cycle_Time_Normal, 0 ) FROM Process_Step_Location_Table where ProcessStep_ID = @processStep_Id ORDER BY PSLT_Priority
				IF @flag = 1 
				BEGIN
					SELECT TOP 1 @date_in = SHIP_DATE FROM SHIPPED_LOT WHERE LOT_ID = @lot_Id ORDER BY SHIP_DATE DESC
				END
				--PRINT ' Assembly-@qty_in = ' + CONVERT( VARCHAR, ISNULL( @qty_in, 0 ) ) + ' Assembly-@ASSEMBLY_LOT_QTY = ' + CONVERT( VARCHAR, ISNULL( @ASSEMBLY_LOT_QTY, 0 ) )

				SET @ASSEMBLY_LOT_QTY = NULL
			END
			--Lots with Process Type Name "TEST" have Qty In and Date In information in the FINAL_TEST_LOT table.
			ELSE IF @ps_type_name = 'Test'
			BEGIN
				SELECT @qty_in = FTL_Qty_In, @date_in = FTL_Date_In, @expectedDateOut = FTL_EXPECTED_DATE FROM Final_Test_Lot WHERE Lot_Id = @lot_Id
				SELECT TOP 1 @processCycleTime = ISNULL( PSLT_Cycle_Time_Normal, 0 ) FROM Process_Step_Location_Table where ProcessStep_ID = @processStep_Id ORDER BY PSLT_Priority
				IF @flag = 1 
				BEGIN
					SELECT TOP 1 @date_in = SHIP_DATE FROM SHIPPED_LOT WHERE LOT_ID = @lot_Id ORDER BY SHIP_DATE DESC
				END
				--PRINT ' Test-@qty_in = ' + CONVERT( VARCHAR, ISNULL( @qty_in, 0 ) )
			END
			--Lots with Process Type Name "Die Bank" OR Class Store or Finished Goods have Qty In and Date In 
			--information in the INVENTORY_LOT table.
			ELSE IF @ps_type_name = 'Class Store' OR  @ps_type_name = 'Mark & Pack' OR @ps_type_name = 'Finished Goods'
			BEGIN
				SELECT @qty_in = IL_Qty_In, @date_in = IL_Date_In FROM Inventory_Lot WHERE Lot_Id = @lot_Id
				IF @flag = 1 
				BEGIN
					SELECT TOP 1 @date_in = SHIP_DATE FROM SHIPPED_LOT WHERE LOT_ID = @lot_Id ORDER BY SHIP_DATE DESC
				END
			END
			--Lots with Process Type Name "Wafer Store" have Wafers as Qty Input. Qty In and Date In for this Lots 
			--available INVENTORY_LOT table. Converting No. of Wafers into the equivalent No. of Dies using 
			--Gross Die/Wafer (which is read from FAB_TABLE using ProcessStep_ID of FAB Process).
			ELSE IF @ps_type_name = 'Wafer Store'
			BEGIN
				SET @gross_die_per_wafer = 1
				SELECT @wafer_qty_in = IL_WAFER_QTY_IN, @date_in = IL_Date_In FROM Inventory_Lot WHERE Lot_Id = @lot_Id
				SELECT TOP 1 @Temp_ProcessStep_Id = ProcessStep_ID FROM fn_get_process_flow_by_processstep_id ( @processStep_Id ) where Process_Type_Name = 'Fab'
				
				--SELECT @gross_die_per_wafer = ISNULL( FT_Gross_Die_Wafer, 1) FROM Fab_Table WHERE ProcessStep_ID = @Temp_ProcessStep_Id
				SELECT @chkVal = ISNUMERIC( ISNULL( Parameter_Value, 1 ) ) FROM Process_Parameter WHERE ProcessStep_ID = @Temp_ProcessStep_Id AND Parameter_Key = 'Gross Die/Wafer'
				IF @chkVal = 1
				BEGIN
					SELECT @gross_die_per_wafer = ISNULL( Parameter_Value, 1 ) FROM Process_Parameter WHERE ProcessStep_ID = @Temp_ProcessStep_Id AND Parameter_Key = 'Gross Die/Wafer'
				END
				IF ( @gross_die_per_wafer = 0 )
				BEGIN
					SET @gross_die_per_wafer = 1
				END	

				IF @flag = 1 
				BEGIN
					SELECT TOP 1 @date_in = SHIP_DATE FROM SHIPPED_LOT WHERE LOT_ID = @lot_Id ORDER BY SHIP_DATE DESC
				END
				SET @qty_in = @wafer_qty_in * @gross_die_per_wafer
				--PRINT ' WS-@qty_in = ' + CONVERT( VARCHAR, ISNULL( @qty_in, 0 ) )
			END
	
			IF @date_in IS NULL 
			BEGIN
				SET @date_in = @currDate
			END

			IF ISDATE( @expectedDateOut ) = 0
			BEGIN	
				SET @expectedDateOut = NULL
			END	

			IF CONVERT( CHAR, @expectedDateOut, 101 ) = '01/01/1900'
			BEGIN
				SET @expectedDateOut = NULL
			END
			--DELETE FROM @fg_Table
			SET @temp = 0
			
			--Getting the availbel Paths to its Finished Good ProcessStep and its Finished Good ProcessStep_IDS 
			--for this particular ProcessStep_ID (Lot's ProcessStep_ID). If this info available in the ##Process_Paths
			-- table, reading from this table. Otherwise, bringing info into ##Process_Paths table and then to @fg_Table
			Select @temp = Count (*) from ##Process_Paths where ProcessStep_ID = @processStep_Id
			--IF ( @temp > 0 )
			--BEGIN
				--INSERT INTO @fg_Table ( path, fg_Process_Step_ID ) SELECT path, Last_Process_Step_ID FROM ##Process_Paths WHERE ProcessStep_ID = @processStep_Id 
			--END
			--ELSE
			IF ( @temp = 0 )	
			BEGIN
				INSERT INTO ##Process_Paths ( ProcessStep_Id, path, Last_Process_Step_ID ) SELECT @processStep_Id, path, Last_Process_Step_ID FROM fn_ct_get_wip_flush_process_paths ( @processStep_Id )	
				--INSERT INTO ##Process_Paths ( ProcessStep_Id, path, Last_Process_Step_ID ) SELECT @processStep_Id, path, Last_Process_Step_ID FROM fn_ct_get_process_paths ( @processStep_Id )
				--INSERT INTO @fg_Table ( path, fg_Process_Step_ID ) SELECT path, Last_Process_Step_ID FROM ##Process_Paths WHERE ProcessStep_ID = @processStep_Id 
			END
	
			--Processing each record in the @fg_Table table
			--SET @fg_cursor = CURSOR FOR SELECT Path, fg_Process_Step_ID FROM @fg_Table 
			SET @fg_cursor = CURSOR FOR SELECT Path, Last_Process_Step_ID FROM ##Process_Paths WHERE ProcessStep_ID = @processStep_Id 
			OPEN @fg_cursor
			FETCH NEXT FROM @fg_cursor INTO @path, @FG_ProcessStep_Id
			WHILE @@FETCH_STATUS = 0
			BEGIN
				SET @total_ship_time = 0
				SET @total_yeild = 1
	
				--Calculating the Ship Time and Target Yield for the path ( from @processStep_Id to @FG_ProcessStep_Id)
				--If this info is available in ##TempTable, bringing from this table. Otherwise calling fn_get_shiptime_yield
				--proc ( which takes, from_process_step_id, to_process_step_id, path as input and Ship Time and Yield as out.
				IF @flag = 0
				BEGIN

					IF @expectedDateOut IS NOT NULL
					BEGIN	
						SET @flag = 1
					END
						
					SELECT @temp_count = Count(*) FROM ##TempTable WHERE ProcessStep_ID = @processStep_Id and ToProcessStep_Id = @FG_ProcessStep_Id
					IF @temp_count >= 1
					BEGIN
						SELECT @total_ship_time = Total_Ship_Time, @total_yeild = Total_Yeild FROM ##TempTable WHERE ProcessStep_ID = @processStep_Id and ToProcessStep_Id = @FG_ProcessStep_Id
					END
					ELSE 
					BEGIN
						SELECT @total_ship_time = Total_Ship_Time, @total_yeild = Total_Yield FROM fn_get_shiptime_yield( @processStep_Id, @FG_ProcessStep_Id, @path, @flag) 
						INSERT INTO ##TempTable( ProcessStep_Id , ToProcessStep_Id , Total_Ship_Time , Total_Yeild ) values ( @processStep_Id, @FG_ProcessStep_Id, @total_ship_time, @total_yeild )
					END
					IF @expectedDateOut IS NULL
					BEGIN
						/*
						This part of the code is for the case when the Lot comes to Fab 1'st Jan and the 
						total cycle time is 120 days. when caliculating the expected fg date we are taking the
						Date In and adding the Cycle Time( 1 st April ). But if the Lot is originaly still in
						Fab due to some reasons we need to caliculate the time from Fab to Fg from the present date
						*/
		
						SET @numberOfDaysInStage = DateDiff( d, @date_in, @currDate )
		
						IF @numberOfDaysInStage <= @processCycleTime
						BEGIN
							SET @fg_date = DATEADD( d, @total_ship_time, @date_in )
						END
						ELSE
						BEGIN
							SET @total_ship_time = @total_ship_time - @processCycleTime
							SET @fg_date = DATEADD( d, @total_ship_time, @currDate )
						END	
					END
					ELSE
					BEGIN
						SET @numberOfDaysInStage = DATEDIFF( d, @currDate, @expectedDateOut )
						IF @numberOfDaysInStage > 0
						BEGIN
							--SET @total_ship_time = @total_ship_time + @numberOfDaysInStage
							SET @fg_date = DATEADD( d, @total_ship_time, @expectedDateOut )
						END
						ELSE
						BEGIN
							SET @fg_date = DATEADD( d, @total_ship_time, @currDate )
						END
					END

					SELECT @fg_qty = FG_QTY FROM FN_WIP_FLUSH_SHIPPABLE_DETAILS ( @lot_Id, @qty_in, @processStep_Id, @ps_type_name, @path, @FG_ProcessStep_Id, @CURR_YEAR, @CURRENT_QTR )

					--SET @fg_qty = @qty_in * @total_yeild

					--PRINT '1- @path = ' + @path + ' @FG_ProcessStep_Id = ' + CONVERT(VARCHAR,@FG_ProcessStep_Id) + ' @fg_qty = ' + CONVERT(VARCHAR,@fg_qty) + '  @flag = ' + CONVERT(VARCHAR,@flag) 
					--PRINT 'FN_WIP_FLUSH_SHIPPABLE_DETAILS( '+ CONVERT(VARCHAR,@lot_Id) + ',' + CONVERT(VARCHAR,@qty_in) + ','  + CONVERT(VARCHAR,@processStep_Id) + ','  + @ps_type_name + ','''  + @path + ''',' + CONVERT(VARCHAR,@FG_ProcessStep_Id) + ',' + CONVERT(VARCHAR,@CURR_YEAR) + ','  + CONVERT(VARCHAR,@CURRENT_QTR) + ')'

					INSERT INTO WIP_Master VALUES ( @lot_Id, @lot_Number, @processStep_Id, @location_id, @qty_in, @date_in, @FG_ProcessStep_Id, @fg_qty, @fg_date, 0 )
				END
				ELSE
				BEGIN
					SELECT @total_ship_time = Total_Ship_Time, @total_yeild = Total_Yield FROM fn_get_shiptime_yield( @processStep_Id, @FG_ProcessStep_Id , @path, @flag ) 

					SELECT @fg_qty = FG_QTY FROM FN_WIP_FLUSH_SHIPPABLE_DETAILS ( @lot_Id, @qty_in, @processStep_Id, @ps_type_name, @path, @FG_ProcessStep_Id, @CURR_YEAR, @CURRENT_QTR )

					--SET @fg_qty = @qty_in * @total_yeild

					--PRINT '2- @path = ' + @path + ' @FG_ProcessStep_Id = ' + CONVERT(VARCHAR,@FG_ProcessStep_Id) + ' @fg_qty = ' + CONVERT(VARCHAR,@fg_qty) + '  @flag = ' + CONVERT(VARCHAR,@flag) 

					SET @fg_date = DATEADD( d, @total_ship_time, @date_in  )
					INSERT INTO WIP_Master VALUES ( @lot_Id, @lot_Number, @processStep_Id, @location_id, @qty_in, @date_in, @FG_ProcessStep_Id, @fg_qty, @fg_date, 0 )
				END
				FETCH NEXT FROM @fg_cursor INTO @path, @FG_ProcessStep_Id
			END --end of @fg_cursor
			CLOSE @fg_cursor
			DEALLOCATE @fg_cursor
		END --end of @ps_type_name
		SET @fg_qty = NULL
		SET @total_ship_time = NULL
		SET @qty_in = NULL
		FETCH NEXT FROM @wip_cursor INTO @lot_Id, @processStep_Id, @location_Id, @lot_Number, @ps_Type_Name, @flag
	END  --@wip_cursor
	CLOSE @wip_cursor
	DEALLOCATE @wip_cursor
	
	DROP TABLE ##TempTable 
	DROP TABLE ##Process_Paths
	--SELECT * FROM WIP_Master
END --end of begin